# -*- coding: utf-8 -*-
from venv1.Public.dbClection import dbClection
import xlrd
from xlutils.copy import copy
import unittest
class DB_Case():

    def db_case(self):
        filename = r'C:\Users\tanjm.ext\PycharmProjects\OWB\TestData\Data.xls'
        excel_open = xlrd.open_workbook(filename)
        table = excel_open.sheet_by_name('Sheet1')
        rows = table.nrows
        print(rows)
        for i in range(2, rows):
            row_content = table.row_values(i)
            refno = row_content[3]
            xzjd_org=dbClection.getOrgid(self,refno)
            # db = dbClection()
            # xzjd_org = db.getOrgid(self,refno)
            # 保单号
            print(len(xzjd_org))
            a = xzjd_org[0]
            print('===================================================================================================')
            print(a)
            # # 电子合同
            # b=xzjd_org[1]
            # # 是否生效
            # c=xzjd_org[2]
            policyno = a['REFNO']
            econtract = a['LETTYPE']
            effective = a['STATCODE']
            CHDRNUM = a['CHDRNUM']
            BILLFREQ = a['BILLFREQ']

            if effective == 'IF':
                effective1 = '生效'
            elif effective == 'DC':
                effective1 = '拒保'
            else:
                effective1 = '其他'

            if econtract == 'ZLPOLPAC':
                econtract1 = econtract
            else:
                econtract1 = '无'

            excelpath = r'C:\Users\tanjm.ext\PycharmProjects\OWB\TestData\Data.xls'
            wtbook = xlrd.open_workbook(excelpath)
            newb = copy(wtbook)
            # sheet = newb.add_sheet('Sheet3')
            sheet = newb.get_sheet(1)
            row = 3
            sheet.write(row, 0, policyno)
            sheet.write(row, 4, CHDRNUM)
            sheet.write(row, 5, effective)
            sheet.write(row, 6, econtract1)
            sheet.write(row, 9, BILLFREQ)
            row = row + 1

            newb.save(excelpath)




